package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.Lernbuero;


public class Dao_Lernbuero 
{
	
	private Dao_Lernbuerogruppe lernbuerogruppeDao =new Dao_Lernbuerogruppe();
	
	public Lernbuero getLernbuero(String lbbz)
	{

	
		
		Connection conn=ConnectionProvider.getConnection();
		PreparedStatement statement=null;
		ResultSet rs=null;
		Lernbuero l= new Lernbuero();
		
		String sql="select * from lernbuero where lernbbz=?";
		try {
			statement = conn.prepareStatement(sql);
			statement.setString(1, lbbz);
			rs=statement.executeQuery();
			if (rs.next())
			{

				l.setLbID(rs.getInt("lbID"));
				l.setLbgruppeID(lernbuerogruppeDao.getLbgruppeID(rs.getInt("lbgruppeID")));
			   
			}	

			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
			finally {
				try {
					rs.close();
					statement.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			;
			return l;
		}
	
	public void getLernbueroaendern (Lernbuero l)
	{
		
		
		Connection conn=ConnectionProvider.getConnection();
		String sql="update lernbuero set lbID=?, lbgruppeID=? where lernbbz=?";
       
		try {
			
			
			PreparedStatement statement = conn.prepareStatement(sql);
			
			statement.setInt(1, l.getLbID());
			statement.setInt(2,l.getLbgruppeID().getLbgruppeID());
		
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public Lernbuero getLernbueroanlegen(Lernbuero l)
	{
		Connection conn=ConnectionProvider.getConnection();
		String sql="insert into Schueler VALUES (?, ?, ?)";
        try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, getNewID());
			statement.setString(2, l.getLernBEZ());
			statement.setInt(3, l.getLbgruppeID().getLbgruppeID());
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return l;
	}
	

	private  int getNewID() 
	{
		Connection conn=ConnectionProvider.getConnection();
		PreparedStatement statement=null;
		ResultSet rs=null;
		int s= -1;
		
		String sql="select max(schuelerID)+1 from schueler";
		try {
			statement = conn.prepareStatement(sql);
			rs=statement.executeQuery();
			if (rs.next())
			{

				s = rs.getInt(1);
				
				

			}	

			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
			finally {
				try {
					rs.close();
					statement.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return s;
	}
	
	

	public Lernbuero getLbID(int int1) 
	{
		// TODO Auto-generated method stub
		return new Lernbuero();
	}

	public Lernbuero getLernbuero(int int1) {
		// TODO Auto-generated method stub
		return null;
	}

}
